import time
from . import ConnPG
import psycopg2
import sys
from sanic.log import logger


class LatelyProject:

    async def lately_odd(self,start_date=None, end_date=None):
        '''默认最近一个月的定单''' 

        if start_date == None:
            start_date = int(time.time())
        if end_date == None:
            end_date = int(time.time()-3600*24*30)

        sql = "select * from view_odd where create_time<%s and create_time>%s"


        result = await ConnPG().fetchall(sql, (start_date, end_date))

        return result

    async def lately_brand(self):
        '''默认最近100个的新添加SKU的品牌''' 


        sql = "select * from view_sku_info LIMIT 100"


        result = await ConnPG().fetchall(sql)

        brand_list = [{'id': i['brand_id'], 'brand_name': i['brand_name']}for i in result]

        brand_list_ = []
        for i in brand_list:
            if i not in brand_list_:
                brand_list_.append(i)

        return brand_list_

    async def get_all_subject(self):
        '''返回所有的subject'''

        sql = "select * from subject"

        result = await ConnPG().fetchall(sql)

        return result

    async def get_all_brand(self):
        '''返回所有的brand'''

        sql = "select * from brand"

        result = await ConnPG().fetchall(sql)

        return result

    async def analyse_subject(self):
        '''返回最近一个月的来往公司在前的所有subject的列表'''
        odd_list = await self.lately_odd()
        subject_list = await self.get_all_subject()
        project_list = [{'id':i['subject_id'], 'subject_name': i['subject_name'] } for i in odd_list]
        project_list_ = []
        for i in project_list:
            if i not in project_list_:
                project_list_.append(i)
        for i in subject_list:
            if i not in project_list_:
                project_list_.append(i)

        return {
                'status': 1,
                'errorCode': None,
                'errorMsg': None,
                'resultBody': project_list_
        }
        # return project_list_

    async def analyse_bran(self):
        '''返回最近一个月使用的品牌'''
        all_brand_list = await self.get_all_brand()
        brand_list = await self.lately_brand()

        for i in all_brand_list:
            if i not in brand_list:
                brand_list.append(i)
        return {
                'status': 1,
                'errorCode': None,
                'errorMsg': None,
                'resultBody': brand_list
        }
    
    async def get_sku_total(self):
        '''获取数据库存中所有商品的剩余总数'''

        sql = '''
            SELECT sku.id AS sku_id, sku.spu_id, spu.spu_name, sku.unit_id, unit.unit_name,
                COALESCE(SUM(CASE WHEN sku_account.in_or_out = 't' THEN sku_account.quantity ELSE -sku_account.quantity END), 0) AS quantity
            FROM sku
            JOIN spu ON sku.spu_id = spu.id
            JOIN unit ON sku.unit_id = unit.id
            LEFT JOIN sku_account ON sku_account.sku_id = sku.id
            GROUP BY sku.id, spu.spu_name, unit.unit_name
            ORDER BY quantity DESC

        '''

        result = await ConnPG().fetchall(sql)

        return result
    

    async def get_sku_total_by_warehouse_id(self, warehouse_id):
        '''获取数据库存中所有商品的剩余总数'''

        sql = '''
            SELECT sku.id AS sku_id, sku.spu_id, spu.spu_name, sku.unit_id, unit.unit_name,
                COALESCE(SUM(CASE WHEN sku_account.in_or_out = 't' THEN sku_account.quantity ELSE -sku_account.quantity END), 0) AS quantity
            FROM sku
            JOIN spu ON sku.spu_id = spu.id
            JOIN unit ON sku.unit_id = unit.id
            JOIN sku_account ON sku_account.sku_id = sku.id
            JOIN storage_location ON sku_account.storage_location_id = storage_location.id
            JOIN warehouse ON storage_location.warehouse_id = warehouse.id
            WHERE warehouse.id = %s
            GROUP BY sku.id, spu.spu_name, unit.unit_name
            ORDER BY quantity DESC

        '''

        result = await ConnPG().fetchall(sql, (warehouse_id,))

        print(warehouse_id)

        print(result)

        return result






    

    
